﻿using System.Data.Entity;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using ToracTechnologies.EntityFramework;
using ToracTechnologies.ExpressionTreeLibrary.DynamicApi.Framework;
using ToracTechnologies.ExpressionTreeLibrary.DynamicApi.Remappers;
using ToracTechnologies.ExpressionTreeLibrary.ExtensionMethods.ExpressionTrees;
using ToracTechnologies.ExpressionTreeLibrary.Remappers;
using SandboxEntities = ToracTechnologies.DataProvider.EntityFramework.SandboxEntities;

namespace ToracTechnologies.ExpressionTree.Testing.EntityFramework
{

    [TestClass]
    public class EntityFrameworkUnitTest
    {

        #region Helper Methods

        /// <summary>
        /// Create data provider
        /// </summary>
        public static EntityFrameworkDP<SandboxEntities> Create()
        {
            return new EntityFrameworkDP<SandboxEntities>(new SandboxEntities(), null, null, false);
        }

        private static readonly int HowManyRecord = 10;

        public static void TruncateTable()
        {
            using (var DP = Create())
            {
                DP.ExecuteRawSql("TRUNCATE TABLE Ref_Test", TransactionalBehavior.EnsureTransaction);
            }
        }

        public static async Task TruncateTableAsync()
        {
            using (var DP = Create())
            {
                await DP.ExecuteRawSqlAsync("TRUNCATE TABLE Ref_Test", TransactionalBehavior.DoNotEnsureTransaction);
            }
        }

        public static void AddRowsHelper(int howMany, bool truncateTableFirst)
        {
            //go truncate the table first
            if (truncateTableFirst)
                TruncateTable();

            using (var DP = Create())
            {
                for (var i = 0; i < howMany; i++)
                {
                    if (i == 0)
                        DP.ExecuteRawSql(@"Insert Into Ref_Test (Description,Description2,CreateDate,BooleanTest,NullId) Values ({0},{1},{2},{3},{4});", TransactionalBehavior.DoNotEnsureTransaction, "Test" + (i + 1), "Test_" + (i + 1), DateTime.Now, true, i);
                    else
                        DP.ExecuteRawSql(@"Insert Into Ref_Test (Description,Description2,CreateDate,BooleanTest) Values ({0},{1},{2},{3});", TransactionalBehavior.DoNotEnsureTransaction, "Test" + (i + 1), "Test_" + (i + 1), DateTime.Now, true);
                }
            }
        }

        public static void Add10Rows(bool truncateTableFirst)
        {
            AddRowsHelper(HowManyRecord, truncateTableFirst);
        }

        public static void AddRows(int howManyRows, bool truncateTableFirst)
        {
            AddRowsHelper(howManyRows, truncateTableFirst);
        }

        private static IEnumerable<Ref_Test> BuildRows(int howMany)
        {
            List<Ref_Test> lst = new List<Ref_Test>();

            for (int i = 0; i < howMany; i++)
            {
                lst.Add(new Ref_Test { Id = i, Description = i.ToString() });
            }

            return lst;
        }

        #endregion

        #region Expression Parameter Remapper

        [TestMethod]
        public void ExpressionParameterRemapperAndAlso_Test1()
        {
            using (var DP = Create())
            {
                //go truncate the table first (this test's the no parameter)
                TruncateTable();

                //add 10 rows for data
                Add10Rows(false);

                const string containsTest2 = "Test2";
                const string containsTest3 = "Test3";

                var expression1 = PropertySelector_Test1Helper<Ref_Test>(x => x.Description, containsTest2);
                var expression2 = PropertySelector_Test1Helper<Ref_Test>(x => x.Description, containsTest3);

                var sql = ExpressionCombiner.CombineExpressions(expression1, ExpressionCombiner.CombineType.AndAlso, expression2);

                var results = DP.Fetch<Ref_Test>(false).AsQueryable().Where(sql).OrderBy(x => x.Id).ToArray();

                Assert.AreEqual(0, results.Count());
            }
        }

        [TestMethod]
        public void ExpressionParameterRemapperAndAlso_Test2()
        {
            using (var DP = Create())
            {
                //go truncate the table first (this test's the no parameter)
                TruncateTable();

                //add 10 rows for data
                Add10Rows(false);

                const string containsTest2 = "Test";

                var expression1 = PropertySelector_Test1Helper<Ref_Test>(x => x.Description, containsTest2);
                var expression2 = PropertySelector_Test1Helper<Ref_Test>(x => x.Description2, "Test_2");

                var sql = ExpressionCombiner.CombineExpressions(expression1, ExpressionCombiner.CombineType.AndAlso, expression2);

                var results = DP.Fetch<Ref_Test>(false).AsQueryable().Where(sql).OrderBy(x => x.Id).ToArray();

                Assert.AreEqual(1, results.Count());
                Assert.AreEqual("Test2", results.ElementAt(0).Description);
                Assert.AreEqual(2, results.ElementAt(0).Id);
            }
        }

        [TestMethod]
        public void ExpressionParameterRemapperOrElse_Test1()
        {
            using (var DP = Create())
            {
                //go truncate the table first (this test's the no parameter)
                TruncateTable();

                //add 10 rows for data
                Add10Rows(false);

                const string containsTest2 = "Test2";
                const string containsTest3 = "Test3";

                var expression1 = PropertySelector_Test1Helper<Ref_Test>(x => x.Description, containsTest2);
                var expression2 = PropertySelector_Test1Helper<Ref_Test>(x => x.Description, containsTest3);

                var sql = ExpressionCombiner.CombineExpressions(expression1, ExpressionCombiner.CombineType.OrElse, expression2);

                var results = DP.Fetch<Ref_Test>(false).AsQueryable().Where(sql).OrderBy(x => x.Id).ToArray();

                Assert.AreEqual(2, results.Count());
                Assert.AreEqual(containsTest2, results.ElementAt(0).Description);
                Assert.AreEqual(containsTest3, results.ElementAt(1).Description);
            }
        }

        [TestMethod]
        public void ExpressionParameterRemapperNot_Test1()
        {
            using (var DP = Create())
            {
                //go truncate the table first (this test's the no parameter)
                TruncateTable();

                //add 10 rows for data
                Add10Rows(false);

                Expression<Func<Ref_Test, bool>> expression1 = x => x.Id == 2;
                Expression<Func<Ref_Test, bool>> expression2 = x => x.Id == 3;

                var sql = ExpressionCombiner.CombineExpressions(expression1, ExpressionCombiner.CombineType.OrElse, expression2);

                //now add a not in front of it
                var notInFrontOfSql = ExpressionCombiner.Not(sql);

                var results = DP.Fetch<Ref_Test>(false).AsQueryable().Where(notInFrontOfSql).OrderBy(x => x.Id).ToArray();

                Assert.AreEqual(8, results.Count());
                Assert.AreEqual(false, results.Any(x => x.Id == 2 || x.Id == 3));
            }
        }

        #endregion

        #region Property Selector For Reusable Code - Used More For A Working Example

        [TestMethod]
        public void PropertySelector_Test1()
        {
            using (var DP = Create())
            {
                //go truncate the table first (this test's the no parameter)
                TruncateTable();

                //add 10 rows for data
                Add10Rows(false);

                const string containsTest = "Test2";

                var expression = PropertySelector_Test1Helper<Ref_Test>(x => x.Description, containsTest);

                var results = DP.Fetch<Ref_Test>(false).AsQueryable().Where(expression).ToArray();

                Assert.AreEqual(1, results.Count());
                Assert.AreEqual(containsTest, results.ElementAt(0).Description);
            }
        }

        [TestMethod]
        public void PropertySelector_Test2()
        {
            using (var DP = Create())
            {
                //go truncate the table first (this test's the no parameter)
                TruncateTable();

                //add 10 rows for data
                Add10Rows(false);

                const string containsTest1 = "Test2";
                const string containsTest2 = "Test3";

                var expression = PropertySelector_Test2Helper<Ref_Test>(x => x.Description, containsTest1, containsTest2);

                var results = DP.Fetch<Ref_Test>(false).AsQueryable().Where(expression).OrderBy(x => x.Id).ToArray();

                Assert.AreEqual(2, results.Count());
                Assert.AreEqual(containsTest1, results.ElementAt(0).Description);
                Assert.AreEqual(containsTest2, results.ElementAt(1).Description);
            }
        }

        #region Helper Methods

        private static Expression<Func<T, bool>> PropertySelector_Test1Helper<T>(Expression<Func<T, string>> PropertySelector, string ValueToFilterBy)
        {
            var expressionContains = typeof(string).GetMethod("Contains");

            var expressionWithContains = Expression.Call((MemberExpression)PropertySelector.Body, expressionContains, Expression.Constant(ValueToFilterBy));

            return Expression.Lambda<Func<T, bool>>(expressionWithContains, PropertySelector.Parameters);
        }

        private static Expression<Func<T, bool>> PropertySelector_Test2Helper<T>(Expression<Func<T, string>> PropertySelector, string ValueToFilterBy, string Value2ToFilterBy)
        {
            var expressionContains = typeof(string).GetMethod("Contains");

            var expressionWithContains = Expression.Call((MemberExpression)PropertySelector.Body, expressionContains, Expression.Constant(ValueToFilterBy));

            var combinedExpression = Expression.OrElse(expressionWithContains,
                                                         Expression.Call((MemberExpression)PropertySelector.Body,
                                                                         expressionContains,
                                                                         Expression.Constant(Value2ToFilterBy)));

            return Expression.Lambda<Func<T, bool>>(combinedExpression, PropertySelector.Parameters);
        }

        #endregion

        #endregion

        #region Extension Methods For IQueryable

        [TestMethod]
        public async Task SqlFormatter_Test1()
        {
            using (var DP = Create())
            {
                //go truncate the table first (this test's the no parameter)
                TruncateTable();

                //add 10 rows for data
                Add10Rows(false);

                //add the logger now
                var sb = new StringBuilder();

                DP.SqlLogWriter = x => sb.Append(x);

                Func<string, string> ParseOutTheCompletedInSoWeCanRunEqualOnStringTest = x =>
                    {
                        //find the start of -- Completed
                        int indexOfCompleted = x.LastIndexOf("-- Completed");

                        if (indexOfCompleted != -1)
                        {
                            //remove that part
                            return x.Substring(0, indexOfCompleted);
                        }
                        else
                        {
                            //never found that part, return the string passed in
                            return x;
                        }
                    };

                //note ** entity framework will only put the @Param fields in is if the lambda is set using a variable.
                //so if you hard code field == "Test" it will put that exact string in the sql
                //if you do string s = "Test";...then do field = s...it will use tha parameters

                //** since the sql outputs the timing...executes in 3 ms...we need to strip that part out to run the matching

                //1. test string value               
                await DP.Fetch<Ref_Test>(false).Where(x => x.Description == "jason").ToArrayAsync();

                Assert.AreEqual("SELECT \r\n    [Extent1].[Id] AS [Id], \r\n    [Extent1].[Description] AS [Description], \r\n    [Extent1].[Description2] AS [Description2], \r\n    [Extent1].[CreateDate] AS [CreateDate], \r\n    [Extent1].[BooleanTest] AS [BooleanTest], \r\n    [Extent1].[NullId] AS [NullId]\r\n    FROM [dbo].[Ref_Test] AS [Extent1]\r\n    WHERE 'jason' = [Extent1].[Description]", ParseOutTheCompletedInSoWeCanRunEqualOnStringTest(sb.ToString()));

                sb = new StringBuilder();

                //2 . test date value
                DateTime dt = new DateTime(2013, 1, 1);
                await DP.Fetch<Ref_Test>(false).Where(x => x.CreateDate == dt).ToArrayAsync();
                Assert.AreEqual("SELECT \r\n    [Extent1].[Id] AS [Id], \r\n    [Extent1].[Description] AS [Description], \r\n    [Extent1].[Description2] AS [Description2], \r\n    [Extent1].[CreateDate] AS [CreateDate], \r\n    [Extent1].[BooleanTest] AS [BooleanTest], \r\n    [Extent1].[NullId] AS [NullId]\r\n    FROM [dbo].[Ref_Test] AS [Extent1]\r\n    WHERE [Extent1].[CreateDate] = '1/1/2013 12:00:00 AM'", ParseOutTheCompletedInSoWeCanRunEqualOnStringTest(sb.ToString()));

                sb = new StringBuilder();

                //3 . test number value
                await DP.Fetch<Ref_Test>(false).Where(x => x.Id == 1).ToArrayAsync();

                Assert.AreEqual("SELECT \r\n    [Extent1].[Id] AS [Id], \r\n    [Extent1].[Description] AS [Description], \r\n    [Extent1].[Description2] AS [Description2], \r\n    [Extent1].[CreateDate] AS [CreateDate], \r\n    [Extent1].[BooleanTest] AS [BooleanTest], \r\n    [Extent1].[NullId] AS [NullId]\r\n    FROM [dbo].[Ref_Test] AS [Extent1]\r\n    WHERE 1 = [Extent1].[Id]", ParseOutTheCompletedInSoWeCanRunEqualOnStringTest(sb.ToString()));

                sb = new StringBuilder();

                //4 . test boolean value
                await DP.Fetch<Ref_Test>(false).Where(x => x.BooleanTest.Value).ToArrayAsync();

                Assert.AreEqual("SELECT \r\n    [Extent1].[Id] AS [Id], \r\n    [Extent1].[Description] AS [Description], \r\n    [Extent1].[Description2] AS [Description2], \r\n    [Extent1].[CreateDate] AS [CreateDate], \r\n    [Extent1].[BooleanTest] AS [BooleanTest], \r\n    [Extent1].[NullId] AS [NullId]\r\n    FROM [dbo].[Ref_Test] AS [Extent1]\r\n    WHERE [Extent1].[BooleanTest] = 1", ParseOutTheCompletedInSoWeCanRunEqualOnStringTest(sb.ToString()));
            }
        }

        #endregion

        #region Regular Entity Framework Data Provider Tests

        [TestMethod]
        public void CanConnectToDatabase_Test1()
        {
            using (var DP = Create())
            {
                var result = DP.CanConnectToDatabase(false);

                Assert.AreEqual(result.Item1, true);
                Assert.AreEqual(result.Item2, null);
            }
        }

        [TestMethod]
        public void ExecuteRawSqlWAndWithoutParam_Test1()
        {
            using (var DP = Create())
            {
                //go truncate the table first (this test's the no parameter)
                TruncateTable();

                DP.ExecuteRawSql(@"Insert Into Ref_Test (Description,Description2) Values ({0},{1});", TransactionalBehavior.DoNotEnsureTransaction, "Ref Test 1", "Ref Test 2");

                //make sure there is 1 record
                Assert.AreEqual(1, DP.Fetch<Ref_Test>(false).Count());
            }
        }

        [TestMethod]
        public async Task ExecuteRawSqlAsyncWAndWithoutParam_Test1()
        {
            using (var DP = Create())
            {
                //go truncate the table first (this test's the no parameter)
                await TruncateTableAsync();

                await DP.ExecuteRawSqlAsync(@"Insert Into Ref_Test (Description,Description2) Values ({0},{1});", TransactionalBehavior.DoNotEnsureTransaction, "Ref Test 1", "Ref Test 2");

                //make sure there is 1 record
                Assert.AreEqual(1, DP.Fetch<Ref_Test>(false).Count());
            }
        }

        [TestMethod]
        public void ExecuteRawSqlWithResultsNoParameters_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var result = DP.ExecuteRawSqlWithResults<Ref_Test>("select * from ref_test;");

                Assert.AreEqual(HowManyRecord, result.Count());
            }
        }

        [TestMethod]
        public void ExecuteRawSqlWithResultsWithParameters_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var result = DP.ExecuteRawSqlWithResults<Ref_Test>("select * from ref_test where Id={0};", 5).Single();

                Assert.AreEqual(5, result.Id);
                Assert.AreEqual("Test5", result.Description);
                Assert.AreEqual("Test_5", result.Description2);
            }
        }

        [TestMethod]
        public void DeleteWithSql_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                DP.Delete<Ref_Test>(x => x.Id != HowManyRecord, true);

                var records = DP.GetAll<Ref_Test>(false);

                Assert.AreEqual(1, records.Count());
            }
        }

        [TestMethod]
        public void DeleteMultiple_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var records = DP.Fetch<Ref_Test>(true).Where(x => x.Id == 2 || x.Id == 3).ToArray();
                Assert.AreEqual(2, records.Count());

                DP.Delete<Ref_Test>(records, true);

                Assert.AreEqual(8, DP.GetAll<Ref_Test>(false).Count());
            }
        }

        [TestMethod]
        public void DeleteByEntity_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var record = DP.Find<Ref_Test>(x => x.Id == 5, true).Single();

                DP.Delete(record, true);

                Assert.AreEqual(HowManyRecord - 1, DP.GetAll<Ref_Test>(false).Count());
            }
        }

        [TestMethod]
        public void DeleteByEntity_Test2()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var recordToDelete = DP.Find<Ref_Test>(x => true, true).ToList();

                recordToDelete.ForEach(x => DP.Delete(x, false));

                Assert.AreEqual(HowManyRecord, DP.GetAll<Ref_Test>(false).Count());

                DP.SaveChanges();

                Assert.AreEqual(0, DP.GetAll<Ref_Test>(false).Count());
            }
        }

        [TestMethod]
        public void Add_Test1()
        {
            TruncateTable();

            using (var DP = Create())
            {
                var newRecord1 = new Ref_Test() { Id = 1, Description = "1" };
                var newRecord2 = new Ref_Test() { Id = 2, Description = "2" };
                var newRecord3 = new Ref_Test() { Id = 3, Description = "3", Description2 = "3" };

                DP.Add(newRecord1, false);

                Assert.AreEqual(0, DP.GetAll<Ref_Test>(false).Count());

                DP.SaveChanges();

                Assert.AreEqual(1, DP.GetAll<Ref_Test>(false).Count());

                DP.Add(newRecord2, false);
                DP.Add(newRecord3, false);

                Assert.AreEqual(1, DP.GetAll<Ref_Test>(false).Count());

                DP.SaveChanges();

                Assert.AreEqual(3, DP.GetAll<Ref_Test>(false).Count());
            }
        }

        [TestMethod]
        public void Add_Test2()
        {
            TruncateTable();

            using (var DP = Create())
            {
                var newRecord1 = new Ref_Test() { Id = 1, Description = "1" };
                var newRecord2 = new Ref_Test() { Id = 2, Description = "2" };
                var newRecord3 = new Ref_Test() { Id = 3, Description = "3", Description2 = "3" };

                DP.Add(newRecord1, true);
                Assert.AreEqual(1, DP.GetAll<Ref_Test>(false).Count());

                DP.Add(newRecord2, true);
                Assert.AreEqual(2, DP.GetAll<Ref_Test>(false).Count());

                DP.Add(newRecord3, true);
                Assert.AreEqual(3, DP.GetAll<Ref_Test>(false).Count());
            }
        }

        [TestMethod]
        public async Task AddAsync_Test1()
        {
            TruncateTable();

            using (var DP = Create())
            {
                var newRecord1 = new Ref_Test() { Id = 1, Description = "1" };
                var newRecord2 = new Ref_Test() { Id = 2, Description = "2" };
                var newRecord3 = new Ref_Test() { Id = 3, Description = "3", Description2 = "3" };

                DP.Add(newRecord1, false);

                Assert.AreEqual(0, DP.GetAll<Ref_Test>(false).Count());

                await DP.SaveChangesAsync();

                Assert.AreEqual(1, DP.GetAll<Ref_Test>(false).Count());

                DP.Add(newRecord2, false);
                DP.Add(newRecord3, false);

                Assert.AreEqual(1, DP.GetAll<Ref_Test>(false).Count());

                await DP.SaveChangesAsync();

                Assert.AreEqual(3, DP.GetAll<Ref_Test>(false).Count());
            }
        }

        [TestMethod]
        public void AddRange_Test1()
        {
            TruncateTable();

            using (var DP = Create())
            {
                var newRecord1 = new Ref_Test() { Description = "1" };
                var newRecord2 = new Ref_Test() { Description = "2" };
                var newRecord3 = new Ref_Test() { Description = "3", Description2 = "3" };

                List<Ref_Test> lst = new List<Ref_Test>() { newRecord1, newRecord2, newRecord3 };

                DP.AddRange(lst, false);

                Assert.AreEqual(0, DP.GetAll<Ref_Test>(false).Count());

                DP.SaveChanges();

                Assert.AreEqual(lst.Count, DP.GetAll<Ref_Test>(false).Count());
            }
        }

        [TestMethod]
        public void AddRange_Test2()
        {
            TruncateTable();

            using (var DP = Create())
            {
                var newRecord1 = new Ref_Test() { Description = "1" };
                var newRecord2 = new Ref_Test() { Description = "2" };
                var newRecord3 = new Ref_Test() { Description = "3", Description2 = "3" };

                List<Ref_Test> lst = new List<Ref_Test>() { newRecord1, newRecord2, newRecord3 };

                DP.AddRange(lst, true);

                Assert.AreEqual(lst.Count, DP.GetAll<Ref_Test>(false).Count());
            }
        }

        [TestMethod]
        public void AddOrUpdate_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var newRecord1 = new Ref_Test() { Id = -1, Description = "1" };
                var newRecord2 = new Ref_Test() { Id = -1, Description = "2" };

                DP.AddOrUpdate(newRecord1, true, false);
                Assert.AreEqual(HowManyRecord, DP.GetAll<Ref_Test>(false).Count());

                DP.AddOrUpdate(newRecord2, true, true);

                Assert.AreEqual(HowManyRecord + 2, DP.GetAll<Ref_Test>(false).Count());
            }
        }

        [TestMethod]
        public void AddOrUpdate_Test2()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var newRecord1 = DP.Single<Ref_Test>(x => x.Id == 5, true);
                var newRecord2 = new Ref_Test() { Id = -1, Description = "2" };

                const string changestring = "jason";

                newRecord1.Description2 = changestring;

                DP.AddOrUpdate(newRecord2, true, true);

                Assert.AreEqual(HowManyRecord + 1, DP.GetAll<Ref_Test>(false).Count());

                Assert.AreEqual(changestring, DP.Single<Ref_Test>(x => x.Id == 5, false).Description2);
            }
        }

        [TestMethod]
        public void AddOrUpdate_Test3()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var updateRecord1 = DP.Single<Ref_Test>(x => x.Id == 5, true);
                var updateRecord2 = DP.Single<Ref_Test>(x => x.Id == 6, true);

                const string changestring = "jason";

                updateRecord1.Description2 = changestring;
                updateRecord2.Description2 = changestring;

                DP.AddOrUpdate(updateRecord1, false, false);
                DP.AddOrUpdate(updateRecord2, false, false);

                DP.SaveChanges();

                Assert.AreEqual(changestring, DP.Single<Ref_Test>(x => x.Id == 5, false).Description2);
                Assert.AreEqual(changestring, DP.Single<Ref_Test>(x => x.Id == 6, false).Description2);
            }
        }

        [TestMethod]
        public void AddOrUpdateRange_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var newRecord1 = new Ref_Test() { Id = -1, Description = "1" };
                var newRecord2 = new Ref_Test() { Id = -1, Description = "2" };

                var lst = new Ref_Test[] { newRecord1, newRecord2 };

                DP.AddOrUpdateRange(lst, x => x.Id == -1, true);

                Assert.AreEqual(HowManyRecord + lst.Count(), DP.GetAll<Ref_Test>(false).Count());
            }
        }

        [TestMethod]
        public void AddOrUpdateRange_Test2()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                const string changestring = "jason";

                var newRecord1 = DP.Single<Ref_Test>(x => x.Id == 1, true);
                var newRecord2 = DP.Single<Ref_Test>(x => x.Id == 2, true);

                newRecord1.Description = changestring;
                newRecord2.Description2 = changestring;

                var lst = new Ref_Test[] { newRecord1, newRecord2 };

                DP.AddOrUpdateRange(lst, x => x.Id == -1, true);

                Assert.AreEqual(changestring, DP.Single<Ref_Test>(x => x.Id == 1, false).Description);
                Assert.AreEqual(changestring, DP.Single<Ref_Test>(x => x.Id == 2, false).Description2);
            }
        }

        [TestMethod]
        public void Single_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var record = DP.Single<Ref_Test>(x => x.Id == 1, false);

                Assert.AreEqual(1, record.Id);
                Assert.AreEqual("Test1", record.Description);
                Assert.AreEqual("Test_1", record.Description2);
            }
        }

        [TestMethod]
        public async Task SingleAsync_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var record = await DP.SingleAsync<Ref_Test>(x => x.Id == 1, false);

                Assert.AreEqual(1, record.Id);
                Assert.AreEqual("Test1", record.Description);
                Assert.AreEqual("Test_1", record.Description2);
            }
        }

        [TestMethod]
        public void SingleOrDefault_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var record = DP.SingleOrDefault<Ref_Test>(x => x.Id == 1, false);

                Assert.AreEqual(1, record.Id);
                Assert.AreEqual("Test1", record.Description);
                Assert.AreEqual("Test_1", record.Description2);
            }
        }

        [TestMethod]
        public void SingleOrDefault_Test2()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var record = DP.SingleOrDefault<Ref_Test>(x => x.Id == 100, false);

                Assert.IsNull(record);
            }
        }

        [TestMethod]
        public async Task SingleOrDefaultAsync_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var record = await DP.SingleOrDefaultAsync<Ref_Test>(x => x.Id == 1, false);

                Assert.AreEqual(1, record.Id);
                Assert.AreEqual("Test1", record.Description);
                Assert.AreEqual("Test_1", record.Description2);
            }
        }

        [TestMethod]
        public async Task SingleOrDefaultAsync_Test2()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var record = await DP.SingleOrDefaultAsync<Ref_Test>(x => x.Id == 100, false);

                Assert.IsNull(record);
            }
        }

        [TestMethod]
        public void First_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var record = DP.First<Ref_Test>(x => x.Id == 1, false);

                Assert.AreEqual(1, record.Id);
                Assert.AreEqual("Test1", record.Description);
                Assert.AreEqual("Test_1", record.Description2);
            }
        }

        [TestMethod]
        public void FirstOrDefault_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var record = DP.FirstOrDefault<Ref_Test>(x => x.Id == 1, false);

                Assert.AreEqual(1, record.Id);
                Assert.AreEqual("Test1", record.Description);
                Assert.AreEqual("Test_1", record.Description2);
            }
        }

        [TestMethod]
        public void FirstOrDefault_Test2()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var record = DP.FirstOrDefault<Ref_Test>(x => x.Id == 100, false);

                Assert.IsNull(record);
            }
        }

        [TestMethod]
        public async Task FirstAsync_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var record = await DP.FirstAsync<Ref_Test>(x => x.Id == 1, false);

                Assert.AreEqual(1, record.Id);
                Assert.AreEqual("Test1", record.Description);
                Assert.AreEqual("Test_1", record.Description2);
            }
        }

        [TestMethod]
        public async Task FirstOrDefaultAsync_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var record = await DP.FirstOrDefaultAsync<Ref_Test>(x => x.Id == 1, false);

                Assert.AreEqual(1, record.Id);
                Assert.AreEqual("Test1", record.Description);
                Assert.AreEqual("Test_1", record.Description2);
            }
        }

        [TestMethod]
        public async Task FirstOrDefaultAsync_Test2()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var record = await DP.FirstOrDefaultAsync<Ref_Test>(x => x.Id == 100, false);

                Assert.IsNull(record);
            }
        }

        [TestMethod]
        public async Task Find_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var records = await DP.Find<Ref_Test>(x => x.Id > 9, false).ToArrayAsync();

                Assert.AreEqual(1, records.Count());
                Assert.AreEqual(10, records.ElementAt(0).Id);
                Assert.AreEqual("Test10", records.ElementAt(0).Description);
                Assert.AreEqual("Test_10", records.ElementAt(0).Description2);
            }
        }

        [TestMethod]
        public async Task Fetch_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var query = DP.Fetch<Ref_Test>(true).Where(x => x.Id > 9);

                var records = await query.ToArrayAsync();

                Assert.AreEqual(1, records.Count());
                Assert.AreEqual(10, records.ElementAt(0).Id);
                Assert.AreEqual("Test10", records.ElementAt(0).Description);
                Assert.AreEqual("Test_10", records.ElementAt(0).Description2);
            }
        }

        [TestMethod]
        public async Task GetAll_Test1()
        {
            TruncateTable();

            Add10Rows(false);

            using (var DP = Create())
            {
                var records = await DP.GetAll<Ref_Test>(false).ToArrayAsync();

                Assert.AreEqual(HowManyRecord, records.Count());
                Assert.AreEqual(1, records.ElementAt(0).Id);
                Assert.AreEqual("Test1", records.ElementAt(0).Description);
                Assert.AreEqual("Test_1", records.ElementAt(0).Description2);
            }
        }

        [TestMethod]
        public void Transaction_Test1()
        {
            TruncateTable();

            using (var DP = Create())
            {
                var recordToAdd = new Ref_Test() { Description = "New Record" };

                DP.StartTransaction();

                DP.Add(recordToAdd, false);

                Assert.AreEqual(0, DP.GetAll<Ref_Test>(false).Count());

                DP.SaveChanges();

                Assert.AreEqual(1, DP.GetAll<Ref_Test>(false).Count());

                DP.RollBackTransaction();

                Assert.AreEqual(0, DP.GetAll<Ref_Test>(false).Count());
            }
        }

        [TestMethod]
        public void Transaction_Test2()
        {
            TruncateTable();

            using (var DP = Create())
            {
                var recordToAdd = new Ref_Test() { Description = "New Record" };

                DP.StartTransaction();

                DP.Add(recordToAdd, false);

                Assert.AreEqual(0, DP.GetAll<Ref_Test>(false).Count());

                DP.SaveChanges();

                Assert.AreEqual(1, DP.GetAll<Ref_Test>(false).Count());

                DP.CommitTransaction();

                Assert.AreEqual(1, DP.GetAll<Ref_Test>(false).Count());
            }
        }

        #endregion

        #region Dynamic Select

        private class TempHolder
        {
            public int? id { get; set; }
            public string txt { get; set; }
            public TempSubObject SubItem { get; set; }
        }

        private class TempSubObject
        {
            public int Id1 { get; set; }
            public int Id2 { get; set; }
        }

        [TestMethod]
        public async Task Merge_Test1()
        {
            Expression<Func<Ref_Test, TempHolder>> thisExp = x => new TempHolder
                {
                    txt = x.Description
                };

            using (var DP = Create())
            {
                //go truncate the table first (this test's the no parameter)
                TruncateTable();

                //add 10 rows for data
                Add10Rows(false);

                var thisRecord = await DP.EFContext.Ref_Test.Select(thisExp).FirstAsync();

                Assert.AreEqual("Test1", thisRecord.txt);
                Assert.AreEqual(null, thisRecord.id);

                //now modify the select
                thisExp = thisExp.Merge(x => new TempHolder
                     {
                         id = x.Id
                     }, ExpressionRemapperShared.ExpressionMemberInitMergerPosition.Before);

                //now rerun the query
                var thisRecord2 = await DP.EFContext.Ref_Test.Select(thisExp).FirstAsync();

                Assert.AreEqual("Test1", thisRecord2.txt);
                Assert.AreEqual(1, thisRecord2.id);
            }
        }

        [TestMethod]
        public async Task MergeSubObject_Test1()
        {
            Expression<Func<Ref_Test, TempHolder>> baseExp = x => new TempHolder
            {
                id = x.Id,
                txt = x.Description
            };

            Expression<Func<Ref_Test, TempSubObject>> subItem1 = x => new TempSubObject
            {
                Id1 = x.Id
            };

            Expression<Func<Ref_Test, TempSubObject>> subItem2 = x => new TempSubObject
            {
                Id2 = 10
            };

            //now let's merger the 2 sub items
            var mergedSubItems = subItem1.Merge(subItem2, ExpressionRemapperShared.ExpressionMemberInitMergerPosition.After);

            //now merge the sub items with the base object
            var mergedBaseWithSubItem = baseExp.MergeSubObject(mergedSubItems, x => x.SubItem, ExpressionRemapperShared.ExpressionMemberInitMergerPosition.After);

            using (var DP = Create())
            {
                //add 10 rows for data
                Add10Rows(true);

                var thisRecord = await DP.EFContext.Ref_Test.Select(mergedBaseWithSubItem).FirstAsync();

                Assert.AreEqual("Test1", thisRecord.txt);
                Assert.AreEqual(1, thisRecord.id);
                Assert.AreEqual(1, thisRecord.SubItem.Id1);
                Assert.AreEqual(10, thisRecord.SubItem.Id2);
            }
        }

        #endregion

        #region Build Connection String

        [TestMethod]
        public void BuildConnectionString_Test1()
        {
            const string ValueToCheckFor = "metadata=res://*/;provider=System.Data.SqlClient;provider connection string=\"Data Source=ServerName123;Initial Catalog=Db123;Integrated Security=True\"";

            Assert.AreEqual(ValueToCheckFor, EntityFrameworkDP.BuildConnectionString("ServerName123", "Db123"));
        }

        #endregion

        #region Auto Detect Test

        [TestMethod]
        public void UpdateRecordWithAutoDetectFalse_Test1()
        {
            //if you have auto detect false (constructor parameter)
            //then grab a record and update. If you dont have  thisContext.ChangeTracker.DetectChanges(); in the save changes it wont update it but wont raise an error

            TruncateTable();

            const string desc = "New Record";
            const string descToUpdateWith = "Update Description";

            using (var DP = Create())
            {
                DP.EFContext.Configuration.AutoDetectChangesEnabled = false;

                var recordToAdd = new Ref_Test() { Description = desc };

                DP.Add(recordToAdd, true);

                var thisRecordToUpdate = DP.Find<Ref_Test>(x => x.Description == desc, true).First();

                thisRecordToUpdate.Description = descToUpdateWith;

                DP.SaveChanges();

                var thisUpdatedRecord = DP.Find<Ref_Test>(x => x.Description == descToUpdateWith, true).FirstOrDefault();

                Assert.IsNotNull(thisUpdatedRecord);
                Assert.AreEqual(descToUpdateWith, thisUpdatedRecord.Description);
            }
        }

        #endregion

    }
}



